A multidimensional database (MDB) is a type of database that is optimized for data warehouse and online analytical processing (OLAP) applications. Multidimensional databases are frequently created using input from existing relational databases. Whereas a relational database is typically accessed using a Structured Query Language (SQL) query, a multidimensional database allows a user to ask questions, such as “How many Aptivas have been sold in Nebraska so far this year?” and similar questions related to summarizing business operations and trends. An OLAP application that accesses data from a multidimensional database is known as a MOLAP (multidimensional OLAP) application.
A multidimensional database management system (MDDBMS), that is also referred to herein as a multidimensional database, implies the ability to rapidly process the data in the database so that answers can be generated quickly. A number of vendors provide products that use multidimensional databases. Data storage and user interface methods vary from vendor to vendor.
Conceptually, a multidimensional database uses an idea of a “data cube” (further referred to herein as a “cube”) to represent dimensions of data available to a user. For example, “sales” could be viewed in the dimensions of product model, geography, time, or some additional dimension. In this example, “sales” implies a measure attribute of the data cube. Other dimensions are seen as feature attributes, which allow setup of focus criteria of the measure attribute. Additionally, a database creator can define hierarchies and levels within a dimension, for example, state and city levels within a regional hierarchy.
As used herein, a data cube comprises an n-dimensional matrix of measure attribute values stored as multidimensional database in a multidimensional database management system.
A conventional MDDBMS is illustrated in FIG. 1. With reference to FIG. 1, an end user PC 10 (networked user computer 10) is connected to a relational ROLAP type database system, i.e., a relational database (ROLAP) 12 and to a multidimensional database system, implementing a data warehouse (MOLAP) 14. Both relational database 12 and data warehouse 14 store data about the same business, but the systems work differently, as will be described below.
Data processing may take place within the database system, a mid-tier server, or the client. In a two-tiered architecture, the user submits a Structure Query Language (SQL) query to the database and receives back the requested data. In a three-tiered architecture, the user submits a request for multidimensional analysis and the ROLAP engine converts the request to SQL for submission to the database.
The operation is performed in reverse: the engine converts the resulting data from SQL to a multidimensional format before it is returned to the client for viewing. As is typical of relational databases, some queries are created and stored in advance. If the desired information is available, then that query will be used, which saves time. Otherwise, the query is created on the fly from the user request.
Since ROLAP uses a relational database, it requires more processing time and/or disk space to perform some of the tasks for which multidimensional databases are designed. However, ROLAP supports larger user groups and greater amounts of data and is often used when these capacities are crucial, such as in a large and complex department of an enterprise.
In the technical field of business intelligence, relational databases such as relational database 12 are often used for selective management of information. Businesses typically operate along multiple lines (customers, products, sales representatives, time etc.). Out of this context, applied mathematics shape a multidimensional array of figures describing the business to be measured in terms of a respective business model.
A conventional relational database 12 provides only two dimensions within a single table or a plurality of tables, which is insufficient for the mathematics associated to the business model. The conventional solution to this problem is to introduce a structure of tables called a “star model”. The star model comprises a centre table that in turn comprises the business. Around the center table, additional related tables are arranged to provide a reference for the dimensions of the array (i.e., customer table, product map etc.).
FIG. 2 (FIGS. 2A, 2B) schematically illustrates a conventional security implementation in a ROLAP environment. System 20 comprises a security table 22, a limiting dimension denoted as organization (ORG) table 23, and a fact table 24. As shown in FIG. 2B, the conventional security table 22 comprises the access limitation of a user in the form of a linkage to a reference table or dimension. In this example, the reference table is a geographical attribute. For illustration, the access is delimited to data of a given Data group 56, where the member within the Geography dimension is “Germany”. Further, this view to the fact table 24 is limited via the ORG table 23 that acts as a data map or translator. The facts data may belong to a dimension (Dim1) 25 comprising, for example, “products”, a dimension (Dim2) 26 comprising, for example, “time”, and a dimension (Dim3) 27 comprising, for example, “clients”.
Although this technology has proven to be useful, it would be desirable to present additional improvements. The data volumes to be analyzed in such a business model are quite huge. Consequently, the on-the-fly calculation from rollup (totals) for larger organization units has not yet been possible due to a lack of available computing power. The common workaround usually stores the result of a rollup in aggregation tables, but the total amount of these aggregation tables grows to the same extent as the demand on new rollups (so-called frozen calculations).
This limitation of the conventional MDDBMS system was a motivation for the development of another conventional database technology, referred to as online analytic processing (OLAP) systems. The OLAP systems pre-calculate a multi-dimensional array in a night batch and store the results in a proprietary way, wherein all predefined aggregations on different levels and even formulas across different aggregation levels are performed. This processing is impossible with a relational system-based OLAP (ROLAP) type system.
Although OLAP and ROLAP technology have proven to be useful, it would be desirable to present additional improvements. A multidimensional system-based OLAP, herein referred to as MOLAP, has its limitations because it cannot store single records or textual information as, for example, descriptive texts, etc.
A multidimensional database 14 (FIG. 1) according to the MOLAP type stores n-dimensional matrices, i.e., one value for each combination of members of each dimension. With a suitably defined data cube, it is not possible to differentiate whether a data cube represents the total value of one record or that of many records. Designing the cube in a way that each cell represents a single record would enlarge the structure to an impractical size, i.e., dimensions with millions of members, wherein an unsatisfying large proportion of cells would not contain a value.
Although both the ROLAP system 12 and the MOLAP system 14 handle business intelligence data, the security handling is different. The internal security concept of relational databases, which is based on SQL-Data Definition Language (DDL), is only able to limit access to tables, but not to parts thereof, for example to particular ranges of rows within the table. In a ROLAP system, access rights are implemented by definition of user groups. ROLAP works satisfactorily only with access to all tables in one business model. In MOLAP type systems, the security management is analogous and treated completely independent from the ROLAP side management. Additionally, often a plurality of MOLAP databases exist in a given business environment, resulting in a respective multiple administration effort.
In the conventional MDDBMS of FIG. 1, there exists no method for providing a consistent handling of security in ROLAP and system 12 and MOLAP system 14. From a security management point of view, however, it is strongly desired to ensure that one and the same user of various business intelligence systems (of the ROLAP and the MOLAP type) does not obtain different levels of information out of different systems. This includes the meanings of the respective content.
In other approaches, the missing integration of security between ROLAP and MOLAP leads to significantly difficult administration. In large companies with specialized user communities that all have access to the underlying relational database (ROLAP), the direct link between authorization and database in a multidimensional database management system leads to an unmanageable situation, apart from the manpower involved.
Conventional “automated security operator systems” that are also referred to herein as ASO systems, exist for mainframe-based systems, i.e., host systems and relational databases. These ASO systems are able to support processes to add and remove users on host-based database systems or add and remove rows in tables, a process, which may manage access limits for the access to the data stored in the database.
ASO systems require the involvement of non-technical staff (usually managers) for automatically managing huge amounts of systems including a respective dedicated data access. However, ASO systems cannot manage the administration of multidimensional databases. Further, ASO systems are not designed to keep a consistent security approach in various systems.
Therefore, conventional systems provide for security in a way that involves management interaction and manual work 13 on the MOLAP side, and inconsistencies between ROLAP and MOLAP security. Often, conventional MOLAP security management must simplify the handling of security to a decision of “free access or no access” to keep the MOLAP system manageable. What is therefore needed is a system, a computer program product, and an associated method for a security transfer tool between a ROLAP type database and a MOLAP type database sharing a subset of common data. The need for such a solution has heretofore remained unsatisfied.